const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 创建Express应用
const app = express();
const PORT = 3001;

// 配置中间件
app.use(express.json());
app.use(express.static(path.join(__dirname)));

// 连接SQLite数据库
const db = new sqlite3.Database(path.join(__dirname, 'quiz.db'), (err) => {
  if (err) {
    console.error('数据库连接错误:', err.message);
  } else {
    console.log('成功连接到SQLite数据库');
    // 初始化表结构
    initDatabase();
  }
});

// 初始化数据库表结构
function initDatabase() {
  // 确保表存在并添加deleted字段（如果不存在）
  db.run(`
    CREATE TABLE IF NOT EXISTS questions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      question TEXT NOT NULL,
      options TEXT NOT NULL,
      correctAnswer TEXT NOT NULL,
      explanation TEXT,
      wrongCount INTEGER DEFAULT 0,
      attemptCount INTEGER DEFAULT 0,
      type TEXT DEFAULT 'single',
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      deleted INTEGER DEFAULT 0,
      bankId INTEGER DEFAULT 0
    )
  `, (err) => {
    if (err) {
      console.error('创建表失败:', err.message);
      // 尝试添加deleted字段（如果表已存在但没有该字段）
      addDeletedColumn();
    } else {
      console.log('表结构已初始化');
    }
  });
}

// 添加deleted字段到questions表（如果不存在）
function addDeletedColumn() {
  db.run(`ALTER TABLE questions ADD COLUMN deleted INTEGER DEFAULT 0`, (err) => {
    if (err) {
      console.error('添加deleted字段失败:', err.message);
    } else {
      console.log('成功添加deleted字段');
    }
  });
}

// API端点：获取所有题目（过滤已删除的）
app.get('/api/questions', (req, res) => {
  db.all('SELECT * FROM questions WHERE deleted = 0 ORDER BY attemptCount ASC, createdAt DESC', (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    // 解析JSON字段
    const questions = rows.map(row => ({
      ...row,
      options: JSON.parse(row.options),
      correctAnswer: JSON.parse(row.correctAnswer)
    }));
    res.json(questions);
  });
});

// API端点：添加新题目
app.post('/api/questions', (req, res) => {
  const { question, options, correctAnswer, explanation, type } = req.body;

  if (!question || !options || !correctAnswer) {
    res.status(400).json({ error: '题目、选项和正确答案是必需的' });
    return;
  }

  const stmt = db.prepare(`
    INSERT INTO questions (question, options, correctAnswer, explanation, type)
    VALUES (?, ?, ?, ?, ?)
  `);

  stmt.run(
    question,
    JSON.stringify(options),
    JSON.stringify(correctAnswer),
    explanation || null,
    type || 'single',
    function(err) {
      if (err) {
        res.status(500).json({ error: err.message });
        return;
      }
      res.json({ id: this.lastID, message: '题目添加成功' });
    }
  );

  stmt.finalize();
});

// API端点：更新题目
app.put('/api/questions/:id', (req, res) => {
  const { id } = req.params;
  const { question, options, correctAnswer, explanation, type, wrongCount, attemptCount } = req.body;

  if (!question || !options || !correctAnswer) {
    res.status(400).json({ error: '题目、选项和正确答案是必需的' });
    return;
  }

  const stmt = db.prepare(`
    UPDATE questions
    SET question = ?, options = ?, correctAnswer = ?, explanation = ?, type = ?, wrongCount = ?, attemptCount = ?
    WHERE id = ? AND deleted = 0
  `);

  stmt.run(
        question,
        JSON.stringify(options),
        JSON.stringify(correctAnswer),
        explanation || null,
        type || 'single',
        wrongCount || 0,
        attemptCount || 0,
        id,
        function(err) {
      if (err) {
        res.status(500).json({ error: err.message });
      return;
    }
    res.json({ changes: this.changes, message: '题目更新成功' });
    }
  );

  stmt.finalize();
});

// API端点：删除题目（逻辑删除）
app.delete('/api/questions/:id', (req, res) => {
  const { id } = req.params;

  const stmt = db.prepare('UPDATE questions SET deleted = 1 WHERE id = ?');
  stmt.run(id, function(err) {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    res.json({ changes: this.changes, message: '题目删除成功' });
  });

  stmt.finalize();
});

// 启动服务器
app.listen(PORT, () => {
  console.log(`服务器运行在 http://localhost:${PORT}`);
});

// 优雅关闭数据库连接
process.on('SIGINT', () => {
  db.close((err) => {
    if (err) {
      console.error('关闭数据库连接错误:', err.message);
    } else {
      console.log('数据库连接已关闭');
    }
    process.exit(0);
  });
});

// // 提供静态文件服务
// app.use(express.static(path.join(__dirname, 'dist')));

// // 为所有其他路由提供index.html，支持SPA路由
// app.get('*', (req, res) => {
//   res.sendFile(path.join(__dirname, 'dist', 'index.html'));
// });